|
Oracle函數
oracle的分析函數over(Partition by...)
Sql代碼
over(Partition by...) 一個超級牛皮的ORACLE特有函數。
最近工作中才接觸到這個功能強大而靈活的函數。
oracle的分析函數over 及開窗函數
一：分析函數over
Oracle從8.1.6開始提供分析函數，分析函數用於計算基於組的某種聚合值，它和聚合函數的不同之處是
對於每個組返回多行，而聚合函數對於每個組只返回一行。
下面通過幾個例子來說明其應用。
1：統計某商店的營業額。
date sale
1 20
2 15
3 14
4 18
5 30
規則：按天統計：每天都統計前面幾天的總額
得到的結果：
DATE SALE SUM
----- -------- ------
1 20 20 --1天
2 15 35 --1天＋2天
3 14 49 --1天＋2天＋3天
4 18 67 .
5 30 97 .
2:統計各班成績第一名的同學信息
NAME CLASS S
----- ----- ----------------------
fda 1 80
ffd 1 78
dss 1 95
cfe 2 74
gds 2 92
gf 3 99
ddd 3 99
adf 3 45
asdf 3 55
3dd 3 78
通過：
--
select * from
(
select name,class,s,rank()over(partition by class order by s desc) mm from t2
)
where mm=1
--
得到結果：
NAME CLASS S MM
----- ----- ---------------------- ----------------------
dss 1 95 1
gds 2 92 1
gf 3 99 1
ddd 3 99 1
註意：
1.在求第一名成績的時候，不能用row_number()，因為如果同班有兩個並列第一，row_number()只返回一個結果
2.rank()和dense_rank()的區別是：
--rank()是跳躍排序，有兩個第二名時接下來就是第四名
--dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名
3.分類統計 (並顯示信息)
A B C
-- -- ----------------------
m a 2
n a 3
m a 2
n b 2
n b 1
x b 3
x b 2
x b 4
h b 3
select a,c,sum(c)over(partition by a) from t2
得到結果：
A B C SUM(C)OVER(PARTITIONBYA)
-- -- ------- ------------------------
h b 3 3
m a 2 4
m a 2 4
n a 3 6
n b 2 6
n b 1 6
x b 3 9
x b 2 9
x b 4 9
如果用sum，group by 則只能得到
A SUM(C)
-- ----------------------
h 3
m 4
n 6
x 9
無法得到B列值
＝＝＝＝＝
select * from test
數據:
A B C
1 1 1
1 2 2
1 3 3
2 2 5
3 4 6
---將B欄位值相同的對應的C 欄位值加總
select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum
from test
A B C C_SUM
1 1 1 1
1 2 2 7
2 2 5 7
1 3 3 3
3 4 6 6
---如果不需要已某個欄位的值分割,那就要用 null
eg: 就是將C的欄位值summary 放在每行後面
select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum
from test
A B C C_SUM
1 1 1 17
1 2 2 17
1 3 3 17
2 2 5 17
3 4 6 17
求個人工資占部門工資的百分比
SQL> select * from salary;
NAME DEPT SAL
---------- ---- -----
a 10 2000
b 10 3000
c 10 5000
d 20 4000
SQL> select name,dept,sal,sal*100/sum(sal) over(partition by dept) percent from salary;
NAME DEPT SAL PERCENT
---------- ---- ----- ----------
a 10 2000 20
b 10 3000 30
c 10 5000 50
d 20 4000 100
二：開窗函數
開窗函數指定了分析函數工作的數據窗口大小，這個數據窗口大小可能會隨著行的變化而變化，舉例如下：
1：
over（order by salary） 按照salary排序進行累計，order by是個默認的開窗函數
over（partition by deptno）按照部門分區
2：
over（order by salary range between 5 preceding and 5 following）
每行對應的數據窗口是之前行幅度值不超過5，之後行幅度值不超過5
例如:對於以下列
aa
1
2
2
2
3
4
5
6
7
9
sum(aa)over（order by aa range between 2 preceding and 2 following）
得出的結果是
AA SUM
---------------------- -------------------------------------------------------
1 10
2 14
2 14
2 14
3 18
4 18
5 22
6 18
7 22
9 9
就是說，對於aa=5的一行 ，sum為 5-1 select t.id,mc,to_char(b.rn)||'/'||t.id)e
2 from test t,
(select rownum rn from (select max(to_number(id)) mid from test) connect by rownum |